# 连接database
# coding=utf-8
import pymysql
import pandas as pd
from xml.dom.minidom import Document
import time
import urllib.parse

conn = pymysql.connect(host="49.235.25.67", user="caiyu",
    password="12345678",database="background",charset="utf8",
    ssl = {
        'ssl': {
            'ca':   'pem/ca.pem',
            'key':  'pem/client-key.pem',
            'cert': 'pem/client-cert.pem'
        }
    }
)
cursor = conn.cursor()
cursor.execute("select id,title from doc")
col = cursor.description
result = cursor.fetchall()
# 将结果转化为DateFrame
df = pd.DataFrame(list(result))
# 关闭游标
conn.close()
# 返回dateframe
for i in result:
    print(i)
# <url>
# <loc>http://www.zhuoweifeng.com</loc>
# <lastmod>2021-01-20T11:31:30+00:00</lastmod>
# <changefreq>daily</changefreq>
# <priority>1.0</priority>
# </url>

doc = Document()
data = doc.createElement('urlset')  # 创建一个标签
doc.appendChild(data)  # 将标签添加到文本中

for i in result:
    url = doc.createElement('url')
    url_loc = doc.createElement('loc')
    name_text = doc.createTextNode('https://www.testingcloud.club/wapi/NewUi/article/%d/%s'%(i[0],urllib.parse.quote(i[1])))
    url_loc.appendChild(name_text)
    
    url_lastmod = doc.createElement('lastmod')
    #2021-01-13T17:09:15+00:00
    name_text = doc.createTextNode(time.strftime("%Y-%m-%dT%H:%M:%S+00:00"))
    url_lastmod.appendChild(name_text)    
    
    url_changefreq = doc.createElement('changefreq')
    name_text = doc.createTextNode('daily')
    url_changefreq.appendChild(name_text)    

    url_priority = doc.createElement('priority')
    name_text = doc.createTextNode('1.0')
    url_priority.appendChild(name_text)    
    
    url.appendChild(url_loc)  
    url.appendChild(url_lastmod) 
    url.appendChild(url_changefreq)  
    url.appendChild(url_priority) 
    data.appendChild(url)  # 将标签添加到父标签中

data.setAttribute('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')
data.setAttribute('xmlns', 'http://www.sitemaps.org/schemas/sitemap/0.9') 
data.setAttribute('xsi:schemaLocation', 'http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd') 

f = open('res.xml', 'w')  
doc.writexml(f, indent='\t', newl='\n', addindent='\t', encoding='utf-8')  # 也可使用上一行的方法
f.close() 